package org.afi.mushroomplayer.db;

import java.util.regex.Pattern;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

	private static final String TAG = "DBHelper";

	public static final int TYPE_ABOUT_LIST = 1;
	public static final int TYPE_ABOUT_SONG = 2;
	public static final String KEY_ID = "mId";
	public static final String KEY_TITLE = "mTitle";
	public static final String KEY_ARTIST = "mArtist";
	public static final String KEY_ALBUM = "mAlbum";
	public static final String KEY_SIZE = "mSize";
	public static final String KEY_URL = "mUrl";
	public static final String KEY_DURATION = "mDuration";
	public static final String KEY_INDEX = "mIndex";
	public static final String KEY_LISTNAME = "mListname";

	public static int VERSION = 1;
	private static final String DB_NAME = "mushroom_db";
	private static final String DB_LIST_TABLE = "mushroom_list";
	
	public String mTableName;

	public DBHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	public DBHelper(Context context, String name, int version, String tableName) {
		this(context, name, null, version);
		VERSION = version;
		mTableName = tableName;
	}

	public DBHelper(Context context, String tableName) {
		this(context, DB_NAME, VERSION, DBHelper.notNumericTableName(tableName));
	}
	
	public DBHelper(Context context) {
		this(context, DB_LIST_TABLE);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(TAG, "onCreate");
		String createTable = null;
		if(!mTableName.equals(DB_LIST_TABLE)) {
			createTable = String.format("CREATE TABLE IF NOT EXISTS %s (%s INTEGER, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT PRIMARY KEY, %s INTEGER, %s INTEGER);",
					mTableName, KEY_ID, KEY_TITLE, KEY_ARTIST,
					KEY_ALBUM, KEY_SIZE, KEY_URL, KEY_DURATION, KEY_INDEX);
		}else {
		
			createTable = String.format(
					"CREATE TABLE IF NOT EXISTS %s (%s INTEGER, %s TEXT PRIMARY KEY, %s INTEGER);",
					mTableName, KEY_ID, KEY_LISTNAME, KEY_INDEX);
		}
		Log.i(TAG, "sql:" + createTable);
		db.execSQL(createTable);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.i(TAG, "onUpgrade");
	}
	
	public static void createListTable(SQLiteDatabase db, String table) {

		if(db != null && table != null) {
			String createTable = String.format("CREATE TABLE IF NOT EXISTS %s (%s INTEGER, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT PRIMARY KEY, %s INTEGER, %s INTEGER);", 
					notNumericTableName(table), KEY_ID, KEY_TITLE, KEY_ARTIST,
					KEY_ALBUM, KEY_SIZE, KEY_URL, KEY_DURATION, KEY_INDEX);
			db.execSQL(createTable);
		}
	}
	
	public static int getCountFromTable(SQLiteDatabase db, String table) {

		if(db != null && table != null) {
			String queryTable = String.format("SELECT count(0) FROM %s;", notNumericTableName(table));
			Cursor cursor = db.rawQuery(queryTable, null);
			cursor.moveToFirst();
			return cursor.getInt(0);
		}
		return 0;
	}
	
	public static String notNumericTableName(String str) {
		Pattern pattern = Pattern.compile("[0-9]*");
		if(pattern.matcher(str).matches()) {
			return "mushro_"+str;
		}else return str;
	}
}
